﻿namespace DataIntegration.Storage.SQL
{
    using System.Data;
    using System.Data.Common;
    using System.Text;

    public abstract class SQLDataStore : IDataStore
    {
        protected SQLDataStore(string connectionString)
        {
            this.ConnectionString = connectionString;
        }

        protected string ConnectionString { get; private set; }

        public abstract DataTable Load(string tableName, string filter);

        public abstract void Clear(string tableName);

        public abstract string Convert(object value);

        public abstract DbConnection GetConnection();

        public abstract DbCommand BuildCommand(string commandText);

        public void Commit(DataTable dataTable)
        {
            if (dataTable.Rows.Count > 0)
            {
                using (DbConnection connection = this.GetConnection())
                {
                    connection.Open();

                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                        StringBuilder commandBuilder = new StringBuilder();
                        commandBuilder.AppendFormat("INSERT INTO {0} VALUES (", dataTable.TableName);

                        foreach (object value in dataRow.ItemArray)
                        {
                            commandBuilder.Append(this.Convert(value));
                            commandBuilder.Append(",");
                        }

                        commandBuilder.Remove(commandBuilder.ToString().Length - 1, 1);
                        commandBuilder.Append(")");

                        DbCommand command = this.BuildCommand(commandBuilder.ToString());
                        command.Connection = connection;
                        command.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}
